Lookerを使いこなすための必須機能「テンプレートフィルタ」を試してみる #looker
いきなりですが、Lookerでの「テンプテートフィルタ」という機能をご存知でしょうか？
「聞いたことはあるけど、使ったことはないなー。」
「あー、パラメーターと一緒に公式Docに載ってるやつね、でも使ったことはないんだよなー。」
というように、名前自体は聞いたことがあるけど実際に使ったことが無い方は多いのではないでしょうか。
しかし、テンプレートフィルタは使いこなすことが出来ると、以下のようなメリットを得られることができます。
- Exploreでグラフを作るユーザーが派生テーブルの条件を動的に切り替えられる
- 例えば、年ごとに派生テーブルを複数作っていたのが、1つの派生テーブルで対応出来るようになる
- ある条件に一致するレコードだけを集計したいメジャーを作っている時、その条件を動的に切り替えられる
- 例えば、”男性だけ”、”女性だけ”、それぞれで絞ったメジャーを複数作成することなく、1つのメジャーだけで絞り込み条件の切り替えが出来るようになる
このように、テンプレートフィルタを使えると、派生テーブルや各フィールドの条件を動的に切り替えることができるようになるため、処理の記述量を少なくするだけでなく、ビジネスユーザーの分析の幅を広げることができます！
ただ、一見使い方がわかりづらく取っ付きにくい機能だと思うため、本ブログで基本的な使い方と事例を3つほどまとめてみたいと思います。
使い方
まず、テンプレートフィルタの使い方を説明します。 ここでは、顧客IDごとに売上をまとめた派生テーブルにテンプレートフィルタを適用させることを考えます。
1.filterの定義
まずは、ベースとなるfilterパラメータを定義します。
テンプレートフィルタは「Explore上にフィルタを設定して、そのフィルタの内容に応じて派生テーブルなどの条件式を変更できる」機能であるため、Exploreで使うことができるフィルタを作成することが必要です。
書き方の例を、以下に示します。
filter: order_region {
type: string
suggest_dimension: region
suggest_explore: orders
}
最低限filterパラメータを機能させるためには、そのフィルタの名称とtypeを指定してあげればOKです。
ただ、よりユーザーにとって使いやすいフィルタにするためには、suggest_dimensionとsuggest_exploreも併せて設定してあげたほうがよいでしょう。
suggest_dimensionに対しては、任意のディメンションを設定することで、下図のようにこのfilterをexplore上で使用するときに、候補値を表示することが出来るようになります。
suggest_exploreは、もしsuggest_dimensionで指定したディメンションが別のexploreに存在する場合に、その別のexplore名をsuggest_exploreで設定することで、別のexploreの値でも候補値を出すことが出来るようになります。
2.作成したfilterを適用したい箇所にLiquidで埋め込む
次に、作成したフィルタをLiquidを用いて埋め込みます。
書き方が特徴的なのですが、WHERE句や、CASE句のWHENの後など、条件式を記述する箇所で{% condition <作成したフィルタ名> %} <フィルタを適用させるフィールド> {% endcondition %}と、記述します。
記述の仕方自体はそれほど難しくはないと思います。
view: customer_facts {
derived_table: {
sql:
SELECT
customer_id,
SUM(sale_price) AS lifetime_spend
FROM
order
WHERE
{% condition order_region %} order.region {% endcondition %}
GROUP BY 1
;;
}
filter: order_region {
type: string
}
}
3.Explore上でフィルタを設定する
LookMLでの定義は2番までの作業で終わりのため、最後にExploreでの適用方法を説明します。
filterで作成したフィルタは、下図のように「FILTER-ONLY FIELDS」として表示されます。
こちらをクリックして通常のフィルタと同様に使用してもらえれば、テンプレートフィルタを設定した箇所に対して絞り込みが行われるようになります！
事例
続いて、実際にどういった場面で役に立つのか、事例を3つ説明していきたいと思います。
WINODW関数を用いた派生テーブルへの適用
例えば、下記のようなSQLを用いた派生テーブルがあったとしましょう。
この派生テーブルの内容としては、country＆city別に売上の合計値を求め、売上の上位順にランキングをつける、という内容です。
view: ranking_by_city_country {
derived_table: {
sql: WITH t1 AS (
SELECT
users.country
,users.city
,SUM(order_items.sale_price) AS total_price
FROM
order_items
LEFT JOIN
users
ON
order_items.user_id = users.id
GROUP BY
1,2
)
SELECT
*
,RANK() OVER (ORDER BY total_price DESC) AS ranking
FROM
t1
ORDER BY
ranking
;;
}
dimension: country {
type: string
sql: ${TABLE}."COUNTRY" ;;
}
dimension: city {
type: string
sql: ${TABLE}."CITY" ;;
}
dimension: total_price {
type: number
sql: ${TABLE}."TOTAL_PRICE" ;;
}
dimension: ranking {
type: number
sql: ${TABLE}."RANKING" ;;
}
}
今回使用したデータから見ると、下図のようにUSAがTOP10を独占していますね。
ここで、「ユーザーが選択した国の中での売上順にランキングをつけたい」ということを考えてみます。 しかし、通常の派生テーブルでは記述したSQLの条件を動的に変更することができないのでランキングを変えることはできない…と思ってしまいますよね。
そこで、テンプレートフィルタの出番です！！ 上記の派生テーブルに対して、以下のようにfilterパラメータとLiquidを記述してみます。
view: ranking_by_city_country {
derived_table: {
sql: WITH t1 AS (
SELECT
users.country
,users.city
,SUM(order_items.sale_price) AS total_price
FROM
order_items
LEFT JOIN
users
ON
order_items.user_id = users.id
GROUP BY
1,2
)
SELECT
*
,RANK() OVER (ORDER BY total_price DESC) AS ranking
FROM
t1
WHERE
{% condition filter_country %} country {% endcondition %}
ORDER BY
ranking
;;
}
filter: filter_country {
type: string
suggest_dimension: country
}
######
## 以下、同上の各フィールドの定義を記述
######
}
これにより、Explore上でユーザーが「filter_country」フィルタを使用して絞り込んだ国の中で、ランキングが定義されるようになります！
通常、こういった別粒度での集計を行う際は、絞り込む条件毎に固定した別テーブルを作らなくてはいけないですが、Lookerでは派生テーブルとテンプレートフィルタの組合せを用いることで、別テーブルを複数作らずに済むことが出来ます。
条件式を持つメジャーへの適用
次に、条件式を持つメジャーへの適用を考えてみます。
以下のように、都市別の売上と、その都市の売上が全体の割合の内何％を占めるのか、ということを出してみたいとします。（下記のLookMLは"New York"だけで実施したときの例です。）
measure: newyork_total_sales {
type: sum
sql: ${sale_price} ;;
filters: {
field: users.city
value: "New York"
}
}
measure: newyork_sales_divide_total_sales {
type: number
value_format_name: percent_2
sql: ${newyork_total_sales} / NULLIF(${total_sales},0) ;;
}
しかし、この書き方はどうも冗長で、他の都市の場合も考えると、管理するメジャーの数がとても多くなってしまうと思います。
そこで、テンプレートフィルタを用いて、ユーザーが選択した都市から、合計値と全体に対する割合を出すことを考えてみます。
まず、以下のようにLookMLを記述します。
ポイントは、CASE WHENとTHENの間に、{% condition ~ %} ~ {% endcondition %}を入れることです。
filter: filter_city {
type: string
suggest_dimension: users.city
}
measure: selected_city_total_sales {
type: sum
sql: CASE
WHEN {% condition filter_city %} ${users.city} {% endcondition %}
THEN ${sale_price}
ELSE 0 END;;
}
measure: selected_city_sales_divide_total_sales {
type: number
value_format_name: percent_2
sql: ${selected_city_total_sales} / NULLIF(${total_sales},0) ;;
}
このように記述することで、New yorkを選んでいるときの結果はもちろんのこと、他の都市を複数選んだ用いた結果も算出することが出来ます！
一応、補足としては、「ユーザーが選択した都市以外で」結果を出すことも可能です。
方法としては、CASE WHENと{% condition ~ %} ~ {% endcondition %}の間にNOTを入れてあげるだけです。
filter: filter_city {
type: string
suggest_dimension: users.city
}
measure: not_selected_city_total_sales {
type: sum
sql: CASE
WHEN NOT {% condition filter_city %} ${users.city} {% endcondition %}
THEN ${sale_price}
ELSE 0 END;;
}
measure: not_selected_city_sales_divide_total_sales {
type: number
value_format_name: percent_2
sql: ${not_selected_city_total_sales} / NULLIF(${total_sales},0) ;;
}
このように、ユーザーが選択した内容に応じて割合を変化させたいときは、テンプレートフィルタが有効に活かせることが伝わったかと思います。
PoP分析でのテンプレートフィルタの活用
最後の3つ目は、テンプレートフィルタをPoP分析に活用する方法です。
まず「PoP分析」とは「Period Over Period」の略で、日本語に訳すと「前期比」という意味になります。 Lookerを始めとしたBIツールを活用する上で、「前期比」を比較したいパターンをよくあると思います。
LookerでのPoP分析の方法は色々とあるのですが、ここではテンプレートフィルタを用いた例を説明したいと思います。
LookerにおけるPoP分析の方法は、英語ですが下記のURLによくまとまっております。
この中で、3. Current Period and Previous Periodの方法を元に、テンプレートフィルタがどう使用されているか見ていきます。
## テンプレートフィルタが関連するところのみ記載
view: pop_parameters {
extends: [order_items]
filter: current_date_range {
type: date
view_label: "_PoP"
label: "1. Current Date Range"
description: "Select the current date range you are interested in. Make sure any other filter on Event Date covers this period, or is removed."
sql: ${period} IS NOT NULL ;;
}
######
## 中略
######
dimension: period {
view_label: "_PoP"
label: "Period"
description: "Pivot me! Returns the period the metric covers, i.e. either the 'This Period' or 'Previous Period'"
type: string
order_by_field: order_for_period
sql:
{% if current_date_range._is_filtered %}
CASE
WHEN {% condition current_date_range %} ${created_raw} {% endcondition %}
THEN 'This {% parameter compare_to %}'
WHEN ${created_date} between ${period_2_start} and ${period_2_end}
THEN 'Last {% parameter compare_to %}'
END
{% else %}
NULL
{% endif %}
;;
}
######
## 中略
######
}
この手法では、パラメータとテンプレートフィルタを用いて「比較する期間の長さ」と「比較する対象（年月週日）」を設定した上で、当期(This)・前期（Last）を決めています。
テンプレートフィルタは上記のdimension:periodのように、CASE WHEN {% condition current_date_range %} ${created_raw} {% endcondition %}と記述することで、データ上の日付が設定した期間（当期・前期）に含まれているか、といったことを確認しています。
この手法自体の解説を本ブログで行うと、かなり長くなってしまうためここでは解説はしませんが…
「テンプレートフィルタは日時に関わるフィールドでも動的に条件変更可能であり、応用例としてPoP分析がある」ということを抑えてもらえればと思います！
最後に
いかがでしたでしょうか！
テンプレートフィルタはLookerならではの機能であり、この機能を使いこなすことでLookerの魅力を更に活かすことが出来ると思います！
このブログを読んで、「あれ、あの条件式をテンプレートフィルタにすれば、ユーザー側でも条件を変更できるかも…？」と思って頂けたら幸いです。
ぜひ、お試しください。
